package cn.itcast.goods.order.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.goods.book.domain.Book;
import cn.itcast.goods.order.domain.Order;
import cn.itcast.goods.order.domain.OrderItem;
import cn.itcast.goods.pager.Expression;
import cn.itcast.goods.pager.PageBean;
import cn.itcast.goods.pager.PageConstants;
import cn.itcast.goods.user.dao.UserDao;
import cn.itcast.goods.user.domain.User;
import cn.mytools.commons.CommonUtils;
import cn.mytools.jdbc.TxQueryRunner;

public class OrderDao {
	private QueryRunner qr = new TxQueryRunner();

	public PageBean<Order> findByUid(String uid, int pc) throws SQLException {
		List<Expression> expList = new ArrayList<Expression>();
		expList.add(new Expression("uid", "=", uid));
		return findByCriteria(expList, pc);
	}

	/**
	 * 通用的查询
	 * 
	 * @param expList
	 * @param pc
	 * @return
	 * @throws SQLException
	 */
	private PageBean<Order> findByCriteria(List<Expression> expList, int pc) throws SQLException {
		int ps = PageConstants.Order_PAGE_SIZE;// 每页记录数

		StringBuilder whereSql = new StringBuilder(" where 1=1");
		List<Object> params = new ArrayList<Object>();// 对应sql中的问号
		for (Expression exp : expList) {
			whereSql.append(" AND").append(" ").append(exp.getName()).append(" ").append(exp.getOperator()).append(" ");

			if (!exp.getOperator().equalsIgnoreCase("is null")) {
				whereSql.append("?");
				params.add(exp.getValue());
			}
		}
		// 查询总记录数
		String sql = "select count(*) from t_order" + whereSql;
		Number number = (Number) qr.query(sql, new ScalarHandler(), params.toArray());
		int tr = number.intValue();// 得到总记录数

		// 按分页查询
		sql = "select * from t_order " + whereSql + " order by ordertime desc limit ? ,? ";
		params.add((pc - 1) * ps);
		params.add(ps);

		List<Map<String, Object>> mapList = qr.query(sql, new MapListHandler(),params.toArray());
		
		List<Order> orderList = new ArrayList<Order>();
		for(Map<String, Object> map : mapList) {
			User user = new UserDao().findByUid((String) map.get("uid"));
			Order order = CommonUtils.toBean(map, Order.class);
			order.setOwner(user);
			loadOrderItem(order);
			orderList.add(order);
		}

		PageBean<Order> pb = new PageBean<Order>();
		pb.setBeanList(orderList);
		pb.setPc(pc);
		pb.setPs(ps);
		pb.setTr(tr);
		// pagebean中的url由service完成
		return pb;
	}

	private void loadOrderItem(Order order) throws SQLException {
		String sql = "select * from t_orderitem where oid = ?";
		List<Map<String, Object>> mapList = qr.query(sql, new MapListHandler(), order.getOid());
		List<OrderItem> orderItemList = toOrderItemList(mapList);
		order.setOrderItemList(orderItemList);
	}

	/**
	 * 把多个Map转换成多个OrderItem
	 * 
	 * @param mapList
	 * @return
	 */
	private List<OrderItem> toOrderItemList(List<Map<String, Object>> mapList) {
		List<OrderItem> orderItemList = new ArrayList<OrderItem>();
		for (Map<String, Object> map : mapList) {
			OrderItem orderItem = toOrderItem(map);
			orderItemList.add(orderItem);
		}
		return orderItemList;
	}

	private OrderItem toOrderItem(Map<String, Object> map) {
		OrderItem orderItem = CommonUtils.toBean(map, OrderItem.class);
		Book book = CommonUtils.toBean(map, Book.class);
		Order order = CommonUtils.toBean(map, Order.class);
		orderItem.setBook(book);
		orderItem.setOrder(order);
		return orderItem;
	}

	public void createOrder(Order order) throws SQLException {
		String sql = "insert into t_order values(?,?,?,?,?,?)";
		Object[] params = { order.getOid(), order.getOrdertime(), order.getTotal(), order.getStatus(),
				order.getAddress(), order.getOwner().getUid() };
		qr.update(sql, params);

		sql = "insert into t_orderitem values(?,?,?,?,?,?,?,?)";
		int len = order.getOrderItemList().size();
		Object[][] objs = new Object[len][];
		for (int i = 0; i < len; i++) {
			OrderItem item = order.getOrderItemList().get(i);
			objs[i] = new Object[] { item.getOrderItemId(), item.getQuantity(), item.getSubtotal(),
					item.getBook().getBid(), item.getBook().getBname(), item.getBook().getCurrPrice(),
					item.getBook().getImage_b(), order.getOid() };
		}
		qr.batch(sql, objs);
	}

	public Order findByOid(String oid) throws SQLException {
		String sql = "select * from t_order where oid = ?";
		Map<String, Object> map = qr.query(sql, new MapHandler(),oid);
		Order order = CommonUtils.toBean(map, Order.class);
		User user = new UserDao().findByUid((String) map.get("uid"));
		order.setOwner(user);
		loadOrderItem(order);
		return order;
	}

	public int findStatus(String oid) throws SQLException {
		String sql = "select status from t_order where oid = ?";
		Number number = (Number) qr.query(sql, new ScalarHandler(), oid);
		return number.intValue();
	}

	public void updateStatus(String oid, int status) throws SQLException {
		String sql = "update t_order set status = ? where oid = ?";
		qr.update(sql, status, oid);
	}

	public PageBean<Order> findAll(int pc) throws SQLException {
		int ps = PageConstants.Order_PAGE_SIZE;// 每页记录数
		// 查询总记录数
		String sql = "select count(*) from t_order";
		Number number = (Number) qr.query(sql, new ScalarHandler());
		int tr = number.intValue();// 得到总记录数
		
		// 按分页查询
		sql = "select * from t_order order by ordertime desc limit ? ,? ";
		List<Map<String, Object>> mapList = qr.query(sql, new MapListHandler(),(pc - 1) * ps,ps);
		
		List<Order> orderList = new ArrayList<Order>();
		for(Map<String, Object> map : mapList) {
			User user = new UserDao().findByUid((String) map.get("uid"));
			Order order = CommonUtils.toBean(map, Order.class);
			order.setOwner(user);
			loadOrderItem(order);
			orderList.add(order);
		}
		
		PageBean<Order> pb = new PageBean<Order>();
		pb.setBeanList(orderList);
		pb.setPc(pc);
		pb.setPs(ps);
		pb.setTr(tr);
		// pagebean中的url由service完成
		return pb;
	}

	public PageBean<Order> findByStatus(int status, int pc) throws SQLException {
		List<Expression> expList = new ArrayList<Expression>();
		expList.add(new Expression("status", "=", status));
		return findByCriteria(expList, pc);
	}

}
